-- =============================================
-- This stored procedure gets current contents for specified NavMenuID
--
-- Modifications
-- 09/03/2003    E.Tatsui   Created
-- =============================================

CREATE          PROCEDURE [dbo].[amsp_CMGetCurrentContents]
    @InNavMenuID numeric
AS

  DECLARE
    @ContentID numeric,
    @HTMLCount integer,
    @TaggedPageLayoutID numeric,
    @LinkCount integer,
    @FileCount integer,
    @IconFileName varchar(255),
    @FileTypeDesc varchar(255),
    @EditPage varchar(255),
    @PreFuseURL varchar(500),
    @RecycleBinID numeric

  SELECT @RecycleBinID = NavMenuID
    FROM Nav_Menu
   WHERE Name = 'Content_Recycle_Bin'

  CREATE TABLE #Contents
  (ContentID numeric,
   PreviousContentID numeric,
   NavMenuID numeric,
   Name varchar(255),
   PublishLocation varchar(255),
   ShowInTemplateFlag char(1),
   WorkflowStatusCode char(1),
   MembersOnlyFlag char(1),
   HTMLCount integer,
   TaggedPageLayoutID integer,
   LinkCount integer,
   FileCount integer,
   IconFileName varchar(255),
   FileTypeDesc varchar(255),
   EditPage varchar(255),
   PreFuseURL varchar(500))

  -- Normal folder
  IF @RecycleBinID <> @InNavMenuID
    INSERT INTO #Contents
    SELECT ContentID,
           PreviousContentID,
           NavMenuID,
           Name,
           PublishLocation,
           ShowInTemplateFlag,
           WorkflowStatusCode,
           MembersOnlyFlag,
           (SELECT count(*) FROM Content_HTML WITH (NOLOCK) WHERE ContentID = a.ContentID),
           (SELECT TOP 1 TaggedPageLayoutID FROM Content_Link  WITH (NOLOCK) WHERE ContentID = a.ContentID),
           (SELECT Count(*) FROM Content_Link WHERE ContentID = a.ContentID),
           (SELECT Count(*) FROM Content_File WHERE ContentID = a.ContentID),
           SPACE(255),
           SPACE(255),
           SPACE(255),
           SPACE(500)
      FROM vCurrent_Content a WITH (NOLOCK)
     WHERE NavMenuID = @InNavMenuID
  ELSE  -- Recycle bin
    INSERT INTO #Contents
    SELECT a.ContentID,
           a.PreviousContentID,
           a.NavMenuID,
           a.Name,
           a.PublishLocation,
           a.ShowInTemplateFlag,
           a.WorkflowStatusCode,
           a.MembersOnlyFlag,
           (SELECT count(*) FROM Content_HTML WITH (NOLOCK) WHERE ContentID = a.ContentID),
           (SELECT TOP 1 TaggedPageLayoutID FROM Content_Link  WITH (NOLOCK) WHERE ContentID = a.ContentID),
           (SELECT Count(*) FROM Content_Link WHERE ContentID = a.ContentID),
           (SELECT Count(*) FROM Content_File WHERE ContentID = a.ContentID),
           SPACE(255),
           SPACE(255),
           SPACE(255),
           SPACE(500)
      FROM vRecycle_Bin a WITH (NOLOCK)

  DECLARE c_Contents CURSOR FOR
   SELECT ContentID,
          HTMLCount,
          TaggedPageLayoutID,
          LinkCount,
          FileCount
     FROM #Contents

  OPEN c_Contents
  FETCH NEXT FROM c_Contents
   INTO @ContentID,
        @HTMLCount,
        @TaggedPageLayoutID,
        @LinkCount,
        @FileCount

  WHILE @@FETCH_STATUS = 0 BEGIN
    IF @TaggedPageLayoutID IS NOT NULL BEGIN
      SET @IconFileName = 'TaggedPage_icon.gif'
      SET @FileTypeDesc = 'Tagged Page'
      SET @EditPage = 'ContentProperties'
    END
    ELSE IF @HTMLCount = 1 AND @FileCount = 0 AND @LinkCount = 0 BEGIN
      SET @IconFileName = 'html_icon.gif'
      SET @FileTypeDesc = 'HTML Document'
      SET @EditPage = 'Editor'
    END
    ELSE IF @HTMLCount = 0 AND @FileCount = 0 AND @LinkCount = 1 BEGIN
      SET @IconFileName = 'link_icon.gif'
      SET @FileTypeDesc = 'Link'
      SET @EditPage = 'ContentProperties'
    END
    ELSE IF @HTMLCount = 0 AND @FileCount = 1 AND @LinkCount = 0 BEGIN
      SELECT @IconFileName = IsNull(b.IconFileName, 'OtherType_icon.gif'),
             @FileTypeDesc = IsNull(b.FileTypeDesc, 'OtherType_icon.gif')
        FROM Content_File a, File_Type_Ref b
       WHERE a.FileTypeCode = b.FileTypeCode
         AND a.ContentID = @ContentID
      SET @EditPage = 'ContentProperties'
    END
    ELSE IF (@HTMLCount + @FileCount + @LinkCount) > 0 BEGIN
      SET @IconFileName = 'list_icon.gif'
      SET @FileTypeDesc = 'Mixed Content'
      SET @EditPage = 'ContentProperties'
    END
    ELSE  BEGIN
      SET @IconFileName = 'none_icon.gif'
      SET @FileTypeDesc = 'Not defined'
    END

    EXECUTE amsp_CMGetFuseURL @InNavMenuID, @ContentID, @PreFuseURL OUTPUT, NULL
    
    UPDATE #Contents
       SET IconFileName = @IconFileName,
           FileTypeDesc = @FileTypeDesc,
           EditPage = @EditPage,
           PreFuseURL = @PreFuseURL
     WHERE ContentID = @ContentID

    FETCH NEXT FROM c_Contents
     INTO @ContentID,
          @HTMLCount,
          @TaggedPageLayoutID,
          @LinkCount,
          @FileCount
  END

  CLOSE c_Contents
  DEALLOCATE c_Contents
  
  SELECT * FROM #Contents ORDER BY Name

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetCurrentContents] TO [IMIS]
GO